/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Persistencia;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author v3550
 */
public class CategoriaDAO {
      
     public CategoriaDAO() throws DAOException {
       try {
             Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        } catch (ClassNotFoundException ex) {
            throw new DAOException("JdbcOdbDriver not found!!");
        }
    }
   
    private static Connection getConnection() throws SQLException {
        //derbyDB sera o nome do diretorio criado localmente
        return DriverManager.getConnection("jdbc:derby:derbyDB");
    }

    public CategoriaDTO gravar(CategoriaDTO c) throws DAOException {
       
        try {
            Connection con = getConnection();
            Statement sta = con.createStatement();
            String sql = "INSERT INTO ARTIGO_CATEGORIA (DESCRICAO) VALUES ("
                    + "'" + c.getNome() + "'"
                    + ")";
            sta.executeUpdate(sql);
            if(setCodigo(c)) // recupera o id inserido.
            {
                //System.out.println(c.toString());
                sta.close();
                con.close();
                return c;
            }
        } catch (SQLException ex) {
            throw new DAOException(ex.getMessage());
        }
        return null;
    }
    
    private boolean setCodigo(CategoriaDTO p) throws DAOException {
        try {
            Connection con = getConnection();
            PreparedStatement stmt = con.prepareStatement(
                    "SELECT ID FROM ARTIGO_CATEGORIA WHERE UCASE(DESCRICAO)=? " 
                    );
            stmt.setString(1, p.getNome().toUpperCase());
            
            ResultSet resultado = stmt.executeQuery();
            if(resultado.next()) {
                int codigo = resultado.getInt("ID");
                p.setId(codigo);
                return true;
            }
            
        } catch (SQLException ex) {
            throw new DAOException("Falha ao adicionar. ->" + ex.getMessage());
        }
        return false;
    }
    
    public List<CategoriaDTO> getCategorias() throws DAOException {
          CategoriaDTO qualquer = null;
          List<CategoriaDTO> list = new LinkedList<CategoriaDTO>();

        try {
            Connection con = getConnection();
            Statement sta = con.createStatement();

            ResultSet res = sta.executeQuery("SELECT ID, DESCRICAO FROM ARTIGO_CATEGORIA ");
            while (res.next()) {
                qualquer = new CategoriaDTO();
                qualquer.setNome(res.getString("DESCRICAO"));
                qualquer.setId(Integer.parseInt(res.getString("ID")));
                list.add(qualquer);
                
            }
            res.close();
            sta.close();
            con.close();
        } catch (Exception ex) {
            throw new DAOException(ex.getMessage());
        }

        return list;
    }
  
}
